﻿//<TunynetCopyright>
//--------------------------------------------------------------
//<copyright>青岛拓宇网络科技有限公司 2005-2008</copyright>
//<version>V0.5</verion>
//<createdate>2008-2-15</createdate>
//<author>mazq</author>
//<email>mazq@Tunynet.com</email>
//<log date="2008-2-15">创建</log>
//--------------------------------------------------------------
//</TunynetCopyright>

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Tunynet.QingdaoCustoms.Common;
using Tunynet.QingdaoCustoms.Data.Utils;
using Tunynet.QingdaoCustoms.Serialization;
using ChinaCustoms.Qingdao.Workflow.Supports.DataProvider;
using ChinaCustoms.Framework.DeluxeWorks.Library.Data;

namespace ChinaCustoms.Qingdao.Workflow.Supports
{
    /// <summary>
    /// 工作流引擎业务部分 DataProvider
    /// </summary>
    public class BusinessDataProvider
    {
        private static BusinessDataProvider _instance = null;
        public static BusinessDataProvider Instance()
        {
            if (_instance == null)
                _instance = new BusinessDataProvider();

            return _instance;
        }

        #region Task

        /// <summary>
        ///创建或更新任务
        /// </summary>
        public void CreateUpdateTask(TaskInfo task, DataProviderAction action)
        {
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_Task_CreateUpdateDelete", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@Action", SqlDbType.Int).Value = (int)action;

                myCommand.Parameters.Add("@TaskGuid", SqlDbType.UniqueIdentifier).Value = task.TaskGuid;

                myCommand.Parameters.Add("@WorkflowGuid", SqlDbType.UniqueIdentifier).Value = task.WorkflowGuid;
                myCommand.Parameters.Add("@TaskName", SqlDbType.NVarChar, 128).Value = task.TaskName;
                myCommand.Parameters.Add("@TaskType", SqlDbType.NVarChar, 128).Value = task.TaskType;
                myCommand.Parameters.Add("@Description", SqlDbType.NVarChar, 3000).Value = task.Description;
                myCommand.Parameters.Add("@ApplicationCodeName", SqlDbType.NVarChar, 128).Value = task.ApplicationCodeName;
                myCommand.Parameters.Add("@SponsorUserName", SqlDbType.NVarChar, 128).Value = task.SponsorUserName;
                myCommand.Parameters.Add("@Status", SqlDbType.Int).Value = (int)task.Status;
                myCommand.Parameters.Add("@ExecutingActivityCodeName", SqlDbType.NVarChar, 128).Value = task.ExecutingActivityCodeName;

                SerializerData serializerData = task.GetSerializerData();
                myCommand.Parameters.Add("@PropertyNames", SqlDbType.NText).Value = serializerData.Keys;
                myCommand.Parameters.Add("@PropertyValues", SqlDbType.NText).Value = serializerData.Values;

                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }
                myCommand.ExecuteNonQuery();

                myConnection.Close();
            }
        }

        /// <summary>
        /// 删除任务 
        /// </summary>
        public void DeleteTask(Guid taskGuid)
        {
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_Task_CreateUpdateDelete", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@Action", SqlDbType.Int).Value = (int)DataProviderAction.Delete;
                myCommand.Parameters.Add("@TaskGuid", SqlDbType.UniqueIdentifier).Value = taskGuid;

                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }

                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }

        /// <summary>
        /// 完成或者终止任务
        /// </summary>
        public void CompleteTask(Guid taskGuid, bool isTerminated)
        {
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_Task_Complete", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@TaskGuid", SqlDbType.UniqueIdentifier).Value = taskGuid;

                if (isTerminated)
                    myCommand.Parameters.Add("@IsTerminated", SqlDbType.Bit).Value = isTerminated;

                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }


        /// <summary>
        /// 尝试完成Task
        /// </summary>
        /// <remarks>
        /// 结束依据：Task的所有SubTask及WorkItem都已经得到处理
        /// </remarks>
        /// <param name="taskGuid">TaskGuid</param>
        public bool TryCompleteTask(Guid taskGuid)
        {
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_Task_TryComplete", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@TaskGuid", SqlDbType.UniqueIdentifier).Value = taskGuid;

                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }

                bool result = false;
                result = Convert.ToBoolean(myCommand.ExecuteScalar());

                myConnection.Close();

                return result;
            }
        }

        /// <summary>
        /// 重新启动已经结束的任务
        /// </summary>
        /// <param name="taskGuid">TaskID</param>
        /// <param name="lastWorkItemGuid">最后结束任务的工作项ID</param>
        public void RestartTask(Guid taskGuid, Guid lastWorkItemGuid)
        {
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_Task_Restart", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@TaskGuid", SqlDbType.UniqueIdentifier).Value = taskGuid;
                myCommand.Parameters.Add("@LastWorkItemGuid", SqlDbType.UniqueIdentifier).Value = lastWorkItemGuid;

                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }



        /// <summary>
        /// 获取任务
        /// </summary>
        public TaskInfo GetTask(Guid? taskGuid, Guid? workflowGuid)
        {
            TaskInfo info = null;
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_Task_Get", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                if (taskGuid.HasValue)
                    myCommand.Parameters.Add("@TaskGuid", System.Data.SqlDbType.UniqueIdentifier).Value = taskGuid;

                if (workflowGuid.HasValue)
                    myCommand.Parameters.Add("@WorkflowGuid", System.Data.SqlDbType.UniqueIdentifier).Value = workflowGuid;

                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }
                using (SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleRow))
                {
                    if (dr.Read())
                    {
                        info = PopulateTaskInfoFromIDataReader(dr);
                    }
                }
                myConnection.Close();

                return info;
            }
        }

        /// <summary>
        /// 通过查询条件获取任务，并用于分页显示
        /// </summary>
        public PagingDataSet<TaskInfo> GetTasks(TaskQuery query)
        {
            using (SqlConnection connection = GetSqlConnection())
            {
                using (SqlCommand command = new SqlCommand("app_Tasks_GetSet", connection))
                {
                    command.CommandType = CommandType.StoredProcedure;

                    StringBuilder sqlPopulateSelect = new StringBuilder("SET Transaction Isolation Level Read UNCOMMITTED ");
                    StringBuilder selectClause = new StringBuilder(" select ");
                    if (query.AllowPaging)
                        selectClause.AppendFormat(" Top {0} ", query.PageSize);
                    else if (query.MaxRecords > 0)
                        selectClause.AppendFormat(" Top {0} ", query.MaxRecords);

                    selectClause.Append(" T.TaskGuid ");

                    StringBuilder fromClause = new StringBuilder();
                    fromClause.Append(" from app_Tasks T ");
                    //if ((string.IsNullOrEmpty(query.WorkItemExecutorUserName) == false) || (query.WorkItemStatuses != null && query.WorkItemStatuses.Length > 0))
                    //{
                    //    fromClause.Append(" join app_WorkItems W on T.TaskGuid= W.TaskGuid ");
                    //}

                    StringBuilder whereClause = new StringBuilder(" WHERE 1 = 1 ");
                    StringBuilder orderClause = new StringBuilder(" ORDER BY ");

                    
                    StringBuilder subWhereClause = new StringBuilder(" where 1=1 ");
                    if (string.IsNullOrEmpty(query.WorkItemExecutorUserName) == false)
                    {
                        subWhereClause.AppendFormat(" and ExecutorUserName='{0}' ", query.WorkItemExecutorUserName);
                    }

                    if (query.WorkItemStatuses != null)
                    {
                        if (query.WorkItemStatuses.Length == 1)
                        {
                            subWhereClause.AppendFormat(" and Status= {0} ", (int)query.WorkItemStatuses[0]);
                        }
                        else
                        {
                            string workItemStatusString = string.Empty;

                            for (int i = 0; i < query.WorkItemStatuses.Length; i++)
                            {
                                workItemStatusString += (int)query.WorkItemStatuses[i] + ",";
                            }

                            if (workItemStatusString.EndsWith(","))
                            {
                                workItemStatusString = workItemStatusString.Substring(0,workItemStatusString.Length-1);
                            }

                            if (workItemStatusString != string.Empty)
                            {
                                subWhereClause.AppendFormat(" and Status in ({0}) ", workItemStatusString);
                            }
                        }
                    }

                    if ((string.IsNullOrEmpty(query.WorkItemExecutorUserName) == false) || (query.WorkItemStatuses != null && query.WorkItemStatuses.Length > 0))
                    {
                        whereClause.AppendFormat(" and T.TaskGuid in (select TaskGuid from app_WorkItems  {0} )",subWhereClause);
                    }
                    
                    if (!string.IsNullOrEmpty(query.TaskName.Trim()))
                        whereClause.AppendFormat(" and T.TaskName like '%{0}%' ", query.TaskName.Trim());

                    if (string.IsNullOrEmpty(query.TaskType) == false)
                    {
                        whereClause.AppendFormat(" and T.TaskType like '%{0}%' ", query.TaskType.Trim());
                    }

                    if (query.ApplicationCodeNames != null && query.ApplicationCodeNames.Length > 0)
                        whereClause.AppendFormat(" and T.ApplicationCodeName in ('{0}') ", string.Join("','", query.ApplicationCodeNames));

                    if (!string.IsNullOrEmpty(query.SponsorUserName.Trim()))
                        whereClause.AppendFormat(" and T.SponsorUserName = '{0}' ", query.SponsorUserName.Trim());
                    
                    if (query.TaskStatus.HasValue)
                        whereClause.AppendFormat(" and T.Status = {0} ", (int)query.TaskStatus);

                    if (query.DateScope.HasValue)
                        whereClause.AppendFormat(" and datediff(day, T.CreateDate, getdate()) <= {0} ", (int)query.DateScope);

                    switch (query.SortBy)
                    {
                        case SortTaskBy.TaskName:
                            orderClause.Append(" T.TaskName ");
                            break;
                        case SortTaskBy.TaskType:
                            orderClause.Append(" T.TaskType ");
                            break;
                        case SortTaskBy.CreateDate:
                            orderClause.Append(" T.CreateDate ");
                            break;
                        case SortTaskBy.LastUpdateDate:
                            orderClause.Append(" T.LastUpdateDate ");
                            break;
                        case SortTaskBy.ApplicationCodeName:
                            orderClause.Append(" T.ApplicationCodeName ");
                            break;
                        case SortTaskBy.SponsorUserName:
                            orderClause.Append(" T.SponsorUserName ");
                            break;
                        default:
                            orderClause.Append(" T.CreateDate ");
                            break;
                    }
                    if (query.SortOrder == SortOrder.Descending)
                    {
                        orderClause.Append(" DESC ");
                    }
                    else
                    {
                        orderClause.Append(" ASC ");
                    }

                    sqlPopulateSelect.Append(selectClause.ToString());
                    sqlPopulateSelect.Append(fromClause.ToString());
                    sqlPopulateSelect.Append(whereClause.ToString());
                    sqlPopulateSelect.Append(orderClause.ToString());

                    command.Parameters.Add("@SqlPopulate", SqlDbType.NText).Value = sqlPopulateSelect.ToString();
                    command.Parameters.Add("@PageIndex", SqlDbType.Int).Value = query.PageIndex;
                    command.Parameters.Add("@PageSize", SqlDbType.Int).Value = query.PageSize;
                    command.Parameters.Add("@TotalRecords", SqlDbType.Int).Direction = ParameterDirection.Output;

                    PagingDataSet<TaskInfo> pds = new PagingDataSet<TaskInfo>();

                    if (connection.State == ConnectionState.Closed)
                    {
                        connection.Open();
                    }
                    using (SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (dr.Read())
                        {
                            pds.Records.Add(PopulateTaskInfoFromIDataReader(dr));
                        }

                        dr.Close();

                        pds.TotalRecords = (int)command.Parameters["@TotalRecords"].Value;
                    }

                    connection.Close();

                    return pds;
                }
            }
        }

        /// <summary>
        /// 获取用户相关的Task
        /// </summary>
        /// <param name="userName">用户名</param>
        public List<TaskInfo> GetUserParticipantTasks(string userName, string applicationCodeName, TaskStatus? taskStatus)
        {
            using (SqlConnection myConnection = GetSqlConnection())
            {
                using (SqlCommand command = new SqlCommand("app_Tasks_GetUserParticipantTasks", myConnection))
                {
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add("@UserName", SqlDbType.NVarChar, 128).Value = userName;

                    if (!string.IsNullOrEmpty(applicationCodeName))
                        command.Parameters.Add("@ApplicationCodeName", SqlDbType.NVarChar, 128).Value = applicationCodeName;

                    if (taskStatus.HasValue)
                        command.Parameters.Add("@Status", SqlDbType.Int).Value = (int)taskStatus;

                    List<TaskInfo> tasks = new List<TaskInfo>();

                    if (myConnection.State == ConnectionState.Closed)
                    {
                        myConnection.Open();
                    }
                    using (SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection))
                    {
                        while (dr.Read())
                        {
                            tasks.Add(PopulateTaskInfoFromIDataReader(dr));
                        }

                        dr.Close();
                    }

                    myConnection.Close();

                    return tasks;
                }
            }
        }

        #endregion


        #region SubTask

        /// <summary>
        /// 添加/修改SubTask
        /// </summary>
        public void CreateUpdateSubTask(SubTaskInfo subTask, DataProviderAction action)
        {
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_SubTask_CreateUpdate", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@Action", SqlDbType.Int).Value = (int)action;
                myCommand.Parameters.Add("@SubTaskGuid", SqlDbType.UniqueIdentifier).Value = subTask.SubTaskGuid;
                myCommand.Parameters.Add("@ParentSubTaskGuid", SqlDbType.UniqueIdentifier).Value = subTask.ParentSubTaskGuid;
                myCommand.Parameters.Add("@ActivityGuid", SqlDbType.UniqueIdentifier).Value = subTask.ActivityGuid;
                myCommand.Parameters.Add("@SubFlowGuid", SqlDbType.UniqueIdentifier).Value = subTask.SubFlowGuid;

                myCommand.Parameters.Add("@OrganizationName", SqlDbType.NVarChar, 128).Value = subTask.OrganizationName;
                myCommand.Parameters.Add("@TaskGuid", SqlDbType.UniqueIdentifier).Value = subTask.TaskGuid;
                myCommand.Parameters.Add("@OrganizationID", SqlDbType.UniqueIdentifier).Value = subTask.OrganizationID;
                myCommand.Parameters.Add("@SponsorUserName", SqlDbType.NVarChar, 128).Value = subTask.SponsorUserName;
                myCommand.Parameters.Add("@SponsorDisplayName", SqlDbType.NVarChar, 128).Value = subTask.SponsorDisplayName;
                myCommand.Parameters.Add("@SponsorFullPath", SqlDbType.NVarChar, 128).Value = subTask.SponsorFullPath;
                myCommand.Parameters.Add("@SubTaskName", SqlDbType.NVarChar, 128).Value = subTask.SubTaskName;
                myCommand.Parameters.Add("@Status", SqlDbType.Int).Value = (int)subTask.Status;

                SerializerData serializerData = subTask.GetSerializerData();
                myCommand.Parameters.Add("@PropertyNames", SqlDbType.NText).Value = serializerData.Keys;
                myCommand.Parameters.Add("@PropertyValues", SqlDbType.NText).Value = serializerData.Values;

                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }
                myCommand.ExecuteNonQuery();

                myConnection.Close();
            }
        }

        public void UpdateSubTaskAcceptDate(Guid subTaskGuid, DateTime acceptDate)
        {
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_SubTask_UpdateAcceptDate", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@AcceptDate", SqlDbType.DateTime).Value = acceptDate;
                myCommand.Parameters.Add("@SubTaskGuid", SqlDbType.UniqueIdentifier).Value = subTaskGuid;
                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }
                myCommand.ExecuteNonQuery();

                myConnection.Close();
            }
        }

        ///// <summary>
        ///// 子流程是否已经完成
        ///// </summary>
        ///// <param name="subFlowGuid">子流程ID</param>
        ///// <param name="excludeSubTaskGuid">排除SubTask=excludeSubTaskGuid后，SubFlow是否已经完成</param>
        //public bool IsSubFlowComplete(Guid subFlowGuid, Guid? excludeSubTaskGuid)
        //{
        //    using (SqlConnection myConnection = GetSqlConnection())
        //    {
        //        SqlCommand myCommand = new SqlCommand("app_SubFlow_IsComplete", myConnection);
        //        myCommand.CommandType = CommandType.StoredProcedure;

        //        myCommand.Parameters.Add("@SubFlowGuid", SqlDbType.UniqueIdentifier).Value = subFlowGuid;

        //        if (excludeSubTaskGuid.HasValue)
        //            myCommand.Parameters.Add("@ExcludeSubTaskGuid", SqlDbType.UniqueIdentifier).Value = excludeSubTaskGuid;

        //        if (myConnection.State == ConnectionState.Closed)
        //        {
        //            myConnection.Open();
        //        }

        //        bool result = false;

        //        result = Convert.ToBoolean(myCommand.ExecuteScalar());

        //        myConnection.Close();

        //        return result;
        //    }
        //}


        /// <summary>
        /// SubTask是否已经完成(或即将完成)
        /// </summary>
        /// <remarks>所有子SubTask完成即SubTask完成</remarks>
        /// <param name="subTaskGuid">子任务ID</param>
        /// <param name="childSubFlowGuid">childSubFlowGuid</param>
        /// <param name="excludeWorkItemGuid">排除WorkItemGuid=excludeWorkItemGuid后，SubTask是否即将完成</param>
        public bool IsSubTaskComplete(Guid subTaskGuid, Guid taskGuid, Guid childSubFlowGuid, Guid? excludeWorkItemGuid)
        {
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_SubTask_IsComplete", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@SubTaskGuid", SqlDbType.UniqueIdentifier).Value = subTaskGuid;
                myCommand.Parameters.Add("@TaskGuid", SqlDbType.UniqueIdentifier).Value = taskGuid;
                myCommand.Parameters.Add("@ChildSubFlowGuid", SqlDbType.UniqueIdentifier).Value = childSubFlowGuid;

                if (excludeWorkItemGuid.HasValue)
                    myCommand.Parameters.Add("@ExcludeWorkItemGuid", SqlDbType.UniqueIdentifier).Value = excludeWorkItemGuid;

                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }

                bool result = false;

                result = Convert.ToBoolean(myCommand.ExecuteScalar());

                myConnection.Close();

                return result;
            }
        }

        /// <summary>
        /// 尝试完成SubTask
        /// </summary>
        /// <param name="subTaskGuid">subTaskGuid</param>
        public void TryCompleteSubTask(Guid subTaskGuid)
        {
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_SubTask_TryComplete", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@SubTaskGuid", SqlDbType.UniqueIdentifier).Value = subTaskGuid;

                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }

        /// <summary>
        /// 完成SubTask
        /// </summary>
        /// <remarks>强制完成SubTask及SubTask下的WorkItem</remarks>
        /// <param name="subTaskGuid">子流程ID</param>
        public void CompleteSubTask(Guid subTaskGuid)
        {
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_SubTask_Complete", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@SubTaskGuid", SqlDbType.UniqueIdentifier).Value = subTaskGuid;

                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }

        /// <summary>
        /// 获取SubTask
        /// </summary>
        /// <param name="subTaskGuid">subTaskGuid</param>
        /// <returns>SubTaskInfo</returns>
        public SubTaskInfo GetSubTask(Guid subTaskGuid)
        {
            SubTaskInfo subFlow = null;
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_SubTask_Get", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@SubTaskGuid", SqlDbType.UniqueIdentifier).Value = subTaskGuid;

                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }
                using (SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleRow))
                {
                    if (dr.Read())
                        subFlow = PopulateSubTaskInfoFromIDataReader(dr);
                }
                myConnection.Close();

                return subFlow;
            }
        }

        /// <summary>
        /// 根据条件获取子流程
        /// </summary>
        /// <param name="activityGuid">活动ID</param>
        /// <param name="status">SubTaskStatus</param>
        /// <returns> SubTaskInfo集合 </returns>
        public List<SubTaskInfo> GetSubTasks(Guid activityGuid, SubTaskStatus? status)
        {
            List<SubTaskInfo> subFlows = new List<SubTaskInfo>();
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_SubTasks_Get", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@ActivityGuid", System.Data.SqlDbType.UniqueIdentifier).Value = activityGuid;

                if (status.HasValue)
                    myCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = (int)status;

                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }
                using (SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (dr.Read())
                    {
                        subFlows.Add(PopulateSubTaskInfoFromIDataReader(dr));
                    }
                }
                myConnection.Close();
            }
            return subFlows;
        }

        /// <summary>
        /// 通过ParentTaskID获取 子任务列表
        /// </summary>
        /// <param name="parentTaskGuiD"></param>
        /// <returns></returns>
        public List<SubTaskInfo> GetSubTasksByParentSubTaskGuid(Guid parentSubTaskGuid, SubTaskStatus? status)
        {
            List<SubTaskInfo> subTasks = new List<SubTaskInfo>();
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_SubTasks_GetByParentSubTaskGuid", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@ParentSubTaskGuid", System.Data.SqlDbType.UniqueIdentifier).Value = parentSubTaskGuid;

                if (status.HasValue)
                    myCommand.Parameters.Add("@Status", System.Data.SqlDbType.Int).Value = (int)status;

                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }
                using (SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (dr.Read())
                    {
                        subTasks.Add(PopulateSubTaskInfoFromIDataReader(dr));
                    }
                }
                myConnection.Close();
            }
            return subTasks;
        }
        #endregion

        #region WorkItems

        /// <summary>
        /// 添加/修改WorkItem
        /// </summary>
        internal void CreateUpdateWorkItem(WorkItemInfo workItemInfo, DataProviderAction action)
        {
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_WorkItem_CreateUpdate", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@Action", SqlDbType.Int).Value = (int)action;

                myCommand.Parameters.Add("@WorkItemGuid", SqlDbType.UniqueIdentifier).Value = workItemInfo.WorkItemGuid;
                myCommand.Parameters.Add("@ActivityGuid", SqlDbType.UniqueIdentifier).Value = workItemInfo.ActivityGuid;
                myCommand.Parameters.Add("@ActivityName", SqlDbType.NVarChar, 128).Value = workItemInfo.ActivityName;
                myCommand.Parameters.Add("@TaskGuid", SqlDbType.UniqueIdentifier).Value = workItemInfo.TaskGuid;
                myCommand.Parameters.Add("@SubTaskGuid", SqlDbType.UniqueIdentifier).Value = workItemInfo.SubTaskGuid;
                myCommand.Parameters.Add("@WorkItemName", SqlDbType.NVarChar, 128).Value = workItemInfo.WorkItemName;
                myCommand.Parameters.Add("@CreatorUserName", SqlDbType.NVarChar, 128).Value = workItemInfo.CreatorUserName;
                myCommand.Parameters.Add("@CreatorDisplayName", SqlDbType.NVarChar, 128).Value = workItemInfo.CreatorDisplayName;
                myCommand.Parameters.Add("@CreatorFullPath", SqlDbType.NVarChar, 128).Value = workItemInfo.CreatorFullPath;
                myCommand.Parameters.Add("@ExecutorUserName", SqlDbType.NVarChar, 128).Value = workItemInfo.ExecutorUserName;
                myCommand.Parameters.Add("@ExecutorDisplayName", SqlDbType.NVarChar, 128).Value = workItemInfo.ExecutorDisplayName;
                myCommand.Parameters.Add("@ExecutorFullPath", SqlDbType.NVarChar, 128).Value = workItemInfo.ExecutorFullPath;
                myCommand.Parameters.Add("@Status", SqlDbType.Int).Value = (int)workItemInfo.Status;
                myCommand.Parameters.Add("@ExecutionResult", SqlDbType.Int).Value = (int)workItemInfo.ExecutionResult;
                myCommand.Parameters.Add("@WorkItemType", SqlDbType.Int).Value = (int)workItemInfo.WorkItemType;
                myCommand.Parameters.Add("@Postil", SqlDbType.NText).Value = workItemInfo.Postil;
                myCommand.Parameters.Add("@PreviousWorkItemGuid", SqlDbType.UniqueIdentifier).Value = workItemInfo.PreviousWorkItemGuid;

                SerializerData serializerData = workItemInfo.GetSerializerData();
                myCommand.Parameters.Add("@PropertyNames", SqlDbType.NText).Value = serializerData.Keys;
                myCommand.Parameters.Add("@PropertyValues", SqlDbType.NText).Value = serializerData.Values;

                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }
                myCommand.ExecuteNonQuery();

                myConnection.Close();
            }
        }

        /// <summary>
        /// 创建WorkItem路径
        /// </summary>
        /// <param name="fromWorkItemID">路径起点</param>
        /// <param name="toWorkItemID">路径终点</param>
        public void CreateWorkItemPath(int fromWorkItemID, int toWorkItemID)
        {
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_WorkItemPath_Create", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@FromWorkItemID", SqlDbType.Int).Value = fromWorkItemID;
                myCommand.Parameters.Add("@ToWorkItemID", SqlDbType.Int).Value = toWorkItemID;

                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }


        /// <summary>
        /// 完成工作项
        /// </summary>
        /// <param name="workItemGuid">工作项ID</param>
        /// <param name="activityIsComplete">工作项所属活动是否已经完成</param>
        public void CompleteWorkItem(Guid workItemGuid, out bool activityIsComplete)
        {
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_WorkItem_Complete", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@WorkItemGuid", SqlDbType.UniqueIdentifier).Value = workItemGuid;
                myCommand.Parameters.Add("@ActivityIsComplete", SqlDbType.Bit).Direction = ParameterDirection.Output;

                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }
                myCommand.ExecuteNonQuery();
                activityIsComplete = Convert.ToBoolean(myCommand.Parameters["@ActivityIsComplete"].Value);

                myConnection.Close();
            }
        }

        /// <summary>
        /// 取消工作项
        /// </summary>
        /// <param name="taskID">TaskID</param>
        /// <param name="activityGuid">ActivityGuid</param>
        /// <param name="subFlowID">SubTaskID</param>
        /// <param name="workItemType">WorkItemType</param>
        /// <param name="excludeWorkItemID">排除的WorkItemID</param>
        public void CancelWorkItems(int taskID, Guid activityGuid, int subFlowID, WorkItemType? workItemType, int excludeWorkItemID)
        {
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_WorkItems_Cancel", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@TaskID", SqlDbType.Int).Value = taskID;
                myCommand.Parameters.Add("@ActivityGuid", SqlDbType.UniqueIdentifier).Value = activityGuid;

                myCommand.Parameters.Add("@SubTaskID", SqlDbType.Int).Value = subFlowID;

                if (workItemType.HasValue)
                    myCommand.Parameters.Add("@WorkItemType", SqlDbType.Int).Value = (int)workItemType;

                if (excludeWorkItemID > 0)
                    myCommand.Parameters.Add("@ExcludeWorkItemID", SqlDbType.Int).Value = excludeWorkItemID;

                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }


        ///// <summary>
        ///// 重新执行活动的所有工作项
        ///// </summary>
        //public void RedoWorkItemsOfActivity(Guid activityGuid)
        //{
        //    using (SqlConnection myConnection = GetSqlConnection())
        //    {
        //        SqlCommand myCommand = new SqlCommand("app_WorkItems_RedoActivity", myConnection);
        //        myCommand.CommandType = CommandType.StoredProcedure;

        //        myCommand.Parameters.Add("@ActivityGuid", SqlDbType.UniqueIdentifier).Value = activityGuid;

        //        if (myConnection.State == ConnectionState.Closed)
        //        {
        //            myConnection.Open();
        //        }
        //        myCommand.ExecuteNonQuery();
        //        myConnection.Close();
        //    }
        //}


        /// <summary>
        /// 重新执行子流程的所有工作项
        /// </summary>
        public void RedoWorkItemsOfSubTask(int subFlowID)
        {
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_WorkItems_RedoSubTask", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@SubTaskID", SqlDbType.Int).Value = subFlowID;

                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }

        /// <summary>
        /// 删除工作项
        /// </summary>
        public void DeleteWorkItem(Guid workItemGuid)
        {
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_WorkItem_Delete", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@WorkItemGuid", SqlDbType.UniqueIdentifier).Value = workItemGuid;

                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }
                myCommand.ExecuteNonQuery();
                myConnection.Close();
            }
        }

        /// <summary>
        /// 获取工作项
        /// </summary>        
        public WorkItemInfo GetWorkItem(Guid workItemGuid)
        {
            WorkItemInfo info = null;
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_WorkItem_Get", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@WorkItemGuid", SqlDbType.UniqueIdentifier).Value = workItemGuid;

                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }
                using (SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleRow))
                {
                    if (dr.Read())
                    {
                        info = PopulateWorkItemInfoFromIDataReader(dr);
                    }
                }
                myConnection.Close();

                return info;
            }
        }

        /// <summary>
        /// 获取活动对应的一个工作项
        /// </summary>        
        public WorkItemInfo GetOneWorkItemOfActivity(Guid activityGuid, WorkItemType? workItemType, WorkItemStatus workItemStatus)
        {
            WorkItemInfo info = null;
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_WorkItem_GetOneOfActivity", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@ActivityGuid", SqlDbType.UniqueIdentifier).Value = activityGuid;

                if (workItemType.HasValue)
                    myCommand.Parameters.Add("@WorkItemType", SqlDbType.Int).Value = (int)workItemType;

                myCommand.Parameters.Add("@Status", SqlDbType.Int).Value = (int)workItemStatus;

                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }
                using (SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleRow))
                {
                    if (dr.Read())
                    {
                        info = PopulateWorkItemInfoFromIDataReader(dr);
                    }
                }
                myConnection.Close();

                return info;
            }
        }

        /// <summary>
        /// 获取活动对应的多个符合条件的工作项
        /// </summary> 
        public List<WorkItemInfo> GetAllWorkItemOfActivity(Guid activityGuid, WorkItemType? workItemType, WorkItemStatus workItemStatus)
        {
            List<WorkItemInfo> workItems = new List<WorkItemInfo>();
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_WorkItem_GetAllOfActivity", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@ActivityGuid", SqlDbType.UniqueIdentifier).Value = activityGuid;

                if (workItemType.HasValue)
                    myCommand.Parameters.Add("@WorkItemType", SqlDbType.Int).Value = (int)workItemType;

                myCommand.Parameters.Add("@Status", SqlDbType.Int).Value = (int)workItemStatus;

                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }
                using (SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (dr.Read())
                    {
                        workItems.Add(PopulateWorkItemInfoFromIDataReader(dr));
                    }
                }
                myConnection.Close();

                return workItems;
            }
        }


        /// <summary>
        /// 获取Task中第一个工作项
        /// </summary>        
        public WorkItemInfo GetFirstWorkItem(Guid taskGuid)
        {
            WorkItemInfo info = null;
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_WorkItem_GetFirstOfTask", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@TaskGuid", SqlDbType.UniqueIdentifier).Value = taskGuid;

                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }
                using (SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection | CommandBehavior.SingleRow))
                {
                    if (dr.Read())
                    {
                        info = PopulateWorkItemInfoFromIDataReader(dr);
                    }
                }
                myConnection.Close();

                return info;
            }
        }

        /// <summary>
        /// 根据条件查询工作项
        /// </summary>
        /// <param name="taskID">TaskID</param>
        /// <param name="status">工作项状态</param>
        /// <param name="executorUserName">执行者用户名</param>
        public List<WorkItemInfo> GetWorkItems(string applicationCodeName, Guid? taskGuid, WorkItemStatus? status, string executorUserName)
        {
            List<WorkItemInfo> workItems = new List<WorkItemInfo>();
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_WorkItems_Get", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                if (!string.IsNullOrEmpty(applicationCodeName))
                    myCommand.Parameters.Add("@ApplicationCodeName", System.Data.SqlDbType.NVarChar, 128).Value = applicationCodeName;

                if (taskGuid.HasValue)
                    myCommand.Parameters.Add("@TaskGuid", SqlDbType.UniqueIdentifier).Value = taskGuid;

                if (status.HasValue)
                    myCommand.Parameters.Add("@Status", SqlDbType.Int).Value = (int)status;

                if (!string.IsNullOrEmpty(executorUserName))
                    myCommand.Parameters.Add("@ExecutorUserName", System.Data.SqlDbType.NVarChar, 128).Value = executorUserName;

                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }
                using (SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (dr.Read())
                    {
                        workItems.Add(PopulateWorkItemInfoFromIDataReader(dr));
                    }
                }
                myConnection.Close();
            }

            return workItems;
        }


        /// <summary>
        /// 根据条件获取工作项
        /// </summary>
        ///<param name="query">工作项条件</param>
        ///<param name="recordCount">符合条件的总的记录数</param>
        public PagingDataSet<WorkItemInfo> GetWorkItems(WorkItemQuery query)
        {
            PagingDataSet<WorkItemInfo> pds = new PagingDataSet<WorkItemInfo>();
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_WorkItems_GetList", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                StringBuilder sqlPopulateSelect = new StringBuilder("SET Transaction Isolation Level Read UNCOMMITTED ");
                StringBuilder selectClause = new StringBuilder(" select ");

                if (query.AllowPaging)
                {
                    selectClause.AppendFormat(" Top {0} ", query.PageSize);
                }


                StringBuilder fromClause;
                StringBuilder whereClause;
                selectClause.Append(" C.WorkItemGuid ");
                fromClause = new StringBuilder(" FROM app_WorkItems C join app_Tasks T on C.TaskGuid= T.TaskGuid ");
                whereClause = new StringBuilder(" WHERE 1 = 1 ");

                if (string.IsNullOrEmpty(query.TaskName) == false)
                {
                    whereClause.AppendFormat(" and T.TaskName like '%{0}%' ", query.TaskName);
                }

                if (string.IsNullOrEmpty(query.TaskType) == false)
                {
                    whereClause.AppendFormat(" and T.TaskType like '%{0}%' ", query.TaskType);
                }

                if (query.TaskStatus.HasValue)
                {
                    whereClause.AppendFormat(" and T.Status= {0} ",(int)query.TaskStatus);
                }

                if (query.ActivityGuid != Guid.Empty)
                {
                    whereClause.AppendFormat(" and C.ActivityGuid = '{0}'", query.ActivityGuid);
                }

                if (string.IsNullOrEmpty(query.CreatorUserName) == false)
                {
                    whereClause.AppendFormat(" and C.CreatorUserName = '{0}' ", query.CreatorUserName);
                }

                if (string.IsNullOrEmpty(query.ExecutorUserName) == false)
                {
                    whereClause.AppendFormat(" and C.ExecutorUserName = '{0}' ", query.ExecutorUserName);
                }

                if (query.SubTaskGuid != Guid.Empty)
                {
                    whereClause.AppendFormat(" and C.SubTaskGuid='{0}' ", query.SubTaskGuid);
                }

                if (query.TaskGuid != Guid.Empty)
                {
                    whereClause.AppendFormat(" and C.TaskGuid='{0}' ", query.TaskGuid);
                }

                if (string.IsNullOrEmpty(query.ApplicationCodeName) == false)
                {
                    whereClause.AppendFormat(" and C.TaskGuid in (select TaskGuid from [app_Tasks] where [ApplicationCodeName]='{0}') ", query.ApplicationCodeName);
                }

                if (query.WorkItemStatus.HasValue)
                {
                    whereClause.AppendFormat(" and C.Status= {0} ", (int)query.WorkItemStatus);
                }

                if (query.WorkItemType.HasValue)
                {
                    whereClause.AppendFormat(" and C.WorkItemType= {0} ", (int)query.WorkItemType);
                }

                StringBuilder orderClause = new StringBuilder(" ORDER BY ");
                switch (query.SortBy)
                {
                    case SortWorkItemBy.ActivityGuid:
                        orderClause.Append(" C.ActivityGuid ");
                        break;
                    case SortWorkItemBy.ActivityName:
                        orderClause.Append(" C.ActivityName ");
                        break;
                    case SortWorkItemBy.TaskGuid:
                        orderClause.Append(" C.TaskGuid ");
                        break;
                    case SortWorkItemBy.TaskName:
                        orderClause.Append(" T.TaskName ");
                        break;
                    case SortWorkItemBy.TaskType:
                        orderClause.Append(" T.TaskType ");
                        break;
                    case SortWorkItemBy.SubTaskGuid:
                        orderClause.Append(" C.SubTaskGuid ");
                        break;
                    case SortWorkItemBy.CreatorUserName:
                        orderClause.Append(" C.CreatorUserName ");
                        break;
                    case SortWorkItemBy.ExecutorUserName:
                        orderClause.Append(" C.ExecutorUserName ");
                        break;
                    case SortWorkItemBy.WorkItemType:
                        orderClause.Append(" C.WorkItemType ");
                        break;
                    case SortWorkItemBy.CreateDate:
                        orderClause.Append(" C.CreateDate ");
                        break;
                    case SortWorkItemBy.LastUpdateDate:
                        orderClause.Append(" C.LastUpdateDate ");
                        break;
                    default:
                        orderClause.Append(" C.CreateDate ");
                        break;
                }

                if (query.SortOrder == SortOrder.Descending)
                {
                    orderClause.Append(" DESC ");
                }
                else 
                {
                    orderClause.Append(" ASC ");
                }

                sqlPopulateSelect.Append(selectClause.ToString());
                sqlPopulateSelect.Append(fromClause.ToString());
                sqlPopulateSelect.Append(whereClause.ToString());
                sqlPopulateSelect.Append(orderClause.ToString());

                myCommand.Parameters.Add("@SqlPopulate", SqlDbType.NText).Value = sqlPopulateSelect.ToString();
                myCommand.Parameters.Add("@PageIndex", SqlDbType.Int).Value = query.PageIndex;
                myCommand.Parameters.Add("@PageSize", SqlDbType.Int).Value = query.PageSize;
                myCommand.Parameters.Add("@TotalRecords", SqlDbType.Int).Direction = ParameterDirection.Output;

                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }
                using (SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (dr.Read())
                    {
                        pds.Records.Add(PopulateWorkItemInfoFromIDataReader(dr));
                    }
                }

                pds.TotalRecords = (int)myCommand.Parameters["@TotalRecords"].Value;
                myConnection.Close();
            }

            return pds;
        }

        /// <summary>
        /// 获取后续工作项
        /// </summary>
        public List<WorkItemInfo> GetNextWorkItems(Guid previousWorkItemGuid)
        {
            return GetNextWorkItems(previousWorkItemGuid,null);
        }

        /// <summary>
        /// 获取后续工作项
        /// </summary>
        public List<WorkItemInfo> GetNextWorkItems(Guid previousWorkItemGuid,WorkItemStatus? workItemStatus)
        {
            List<WorkItemInfo> workItems = new List<WorkItemInfo>();
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_WorkItems_GetNextWorkItems", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@PreviousWorkItemGuid", System.Data.SqlDbType.UniqueIdentifier).Value = previousWorkItemGuid;

                if (workItemStatus.HasValue)
                {
                    myCommand.Parameters.Add("@Status",SqlDbType.Int).Value= (int)workItemStatus;
                }
                
                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }
                using (SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (dr.Read())
                    {
                        workItems.Add(PopulateWorkItemInfoFromIDataReader(dr));
                    }
                }
                myConnection.Close();
            }

            return workItems;
        }

        /// <summary>
        /// 获取所有曾经流转到的该活动原型分解的工作项的工作项
        /// </summary>
        public List<WorkItemInfo> GetPreviousWorkItemsToThisActivityPrototype(WorkItemInfo workItem)
        {
            List<WorkItemInfo> workItems = new List<WorkItemInfo>();
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_WorkItems_GetPreviousWorkItemsToThisActivityPrototype", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@TaskGuid", System.Data.SqlDbType.UniqueIdentifier).Value = workItem.TaskGuid;
                myCommand.Parameters.Add("@ActivityName", System.Data.SqlDbType.NVarChar, 128).Value = workItem.ActivityName;
                myCommand.Parameters.Add("@SubTaskGuid", System.Data.SqlDbType.UniqueIdentifier).Value = workItem.SubTaskGuid;
                myCommand.Parameters.Add("@ExecutorUserName", System.Data.SqlDbType.NVarChar, 128).Value = workItem.ExecutorUserName;

                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }
                using (SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (dr.Read())
                    {
                        workItems.Add(PopulateWorkItemInfoFromIDataReader(dr));
                    }
                }
                myConnection.Close();
            }

            return workItems;
        }

        /// <summary>
        /// 获取SubTask路径中的WorkItem
        /// </summary>
        /// <param name="subFlowID">子流程ID</param>
        public List<WorkItemInfo> GetWorkItemsOfSubTaskPath(int subFlowID)
        {
            List<WorkItemInfo> workItems = new List<WorkItemInfo>();
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_WorkItems_GetWorkItemsOfSubTaskPath", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@SubTaskID", System.Data.SqlDbType.Int).Value = subFlowID;

                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }
                using (SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (dr.Read())
                    {
                        workItems.Add(PopulateWorkItemInfoFromIDataReader(dr));
                    }
                }
                myConnection.Close();
            }

            return workItems;
        }


        /// <summary>
        /// 获取活动的执行人员
        /// </summary>
        /// <param name="activityGuid">活动ID</param>
        /// <returns>执行人员用户名集合</returns>
        public List<string> GetExecutorsOfActivity(Guid activityGuid)
        {
            List<string> executorUserNames = new List<string>();
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_WorkItem_GetExecutorsOfActivity", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                myCommand.Parameters.Add("@ActivityGuid", System.Data.SqlDbType.UniqueIdentifier).Value = activityGuid;

                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }
                using (SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (dr.Read())
                    {
                        executorUserNames.Add(Convert.ToString(dr["ExecutorUserName"]));
                    }
                }
                myConnection.Close();
            }

            return executorUserNames;
        }

        #endregion


        #region 工作流管理平台用户角色

        /// <summary>
        /// 获取工作流管理平台所有用户角色与应用程序对应关系
        /// </summary>
        public Dictionary<string, List<string>> GetManagerRoles()
        {
            Dictionary<string, List<string>> applicationToRoles = new Dictionary<string, List<string>>();
            List<SubTaskInfo> subFlows = new List<SubTaskInfo>();
            using (SqlConnection myConnection = GetSqlConnection())
            {
                SqlCommand myCommand = new SqlCommand("app_WorkflowManagerRoles_Get", myConnection);
                myCommand.CommandType = CommandType.StoredProcedure;

                if (myConnection.State == ConnectionState.Closed)
                {
                    myConnection.Open();
                }
                using (SqlDataReader dr = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    string applicationCodeName;
                    string roleName;

                    while (dr.Read())
                    {
                        applicationCodeName = Convert.ToString(dr["ApplicationCodeName"]);
                        roleName = Convert.ToString(dr["RoleName"]);

                        if (applicationToRoles.ContainsKey(applicationCodeName))
                        {
                            if (!applicationToRoles[applicationCodeName].Contains(roleName))
                                applicationToRoles[applicationCodeName].Add(roleName);
                        }
                        else
                        {
                            List<string> roleNames = new List<string>();
                            roleNames.Add(roleName);
                            applicationToRoles[applicationCodeName] = roleNames;
                        }
                    }
                }
                myConnection.Close();
            }
            return applicationToRoles;
        }

        #endregion


        #region Helper methods & properties

        private SqlConnection GetSqlConnection()
        {
            try
            {
                return SqlHelper.WorkflowConnection;
            }
            catch
            {
                throw new Exception("SQL Connection String is invalid.");
            }
        }

        private TaskInfo PopulateTaskInfoFromIDataReader(IDataReader dr)
        {
            TaskInfo task = new TaskInfo();
            task.TaskGuid = new Guid(Convert.ToString(dr["TaskGuid"]));
            task.WorkflowGuid = new Guid(Convert.ToString(dr["WorkflowGuid"]));
            task.TaskName = Convert.ToString(dr["TaskName"]);
            task.TaskType = Convert.ToString(dr["TaskType"]);
            task.Description = Convert.ToString(dr["Description"]);
            task.CreateDate = Convert.ToDateTime(dr["CreateDate"]);
            task.LastUpdateDate = Convert.ToDateTime(dr["LastUpdateDate"]);
            task.ApplicationCodeName = Convert.ToString(dr["ApplicationCodeName"]);
            task.SponsorUserName = Convert.ToString(dr["SponsorUserName"]);
            task.Status = (TaskStatus)(dr["Status"]);
            task.ExecutingActivityCodeName = Convert.ToString(dr["ExecutingActivityCodeName"]);

            SerializerData serializerData = DataProviderHelper.PopulateSerializerDataFromDataReader(dr);
            task.SetSerializerData(serializerData);

            return task;
        }

        private WorkItemInfo PopulateWorkItemInfoFromIDataReader(IDataReader dr)
        {
            WorkItemInfo workItem = new WorkItemInfo();
            workItem.WorkItemGuid = new Guid(Convert.ToString(dr["WorkItemGuid"]));
            workItem.ActivityGuid = new Guid(Convert.ToString(dr["ActivityGuid"]));
            workItem.ActivityName = Convert.ToString(dr["ActivityName"]);
            workItem.TaskGuid = new Guid(Convert.ToString(dr["TaskGuid"]));
            workItem.SubTaskGuid = new Guid(Convert.ToString(dr["SubTaskGuid"]));
            workItem.WorkItemName = Convert.ToString(dr["WorkItemName"]);
            workItem.CreatorUserName = Convert.ToString(dr["CreatorUserName"]);
            workItem.CreatorDisplayName = Convert.ToString(dr["CreatorDisplayName"]);
            workItem.CreatorFullPath = Convert.ToString(dr["CreatorFullPath"]);
            workItem.ExecutorUserName = Convert.ToString(dr["ExecutorUserName"]);
            workItem.ExecutorDisplayName = Convert.ToString(dr["ExecutorDisplayName"]);
            workItem.ExecutorFullPath = Convert.ToString(dr["ExecutorFullPath"]);
            workItem.Status = (WorkItemStatus)Convert.ToInt32(dr["Status"]);
            workItem.ExecutionResult = (WorkItemExecutionResult)Convert.ToInt32(dr["ExecutionResult"]);
            workItem.WorkItemType = (WorkItemType)(dr["WorkItemType"]);
            workItem.Postil = Convert.ToString(dr["Postil"]);
            workItem.CreateDate = Convert.ToDateTime(dr["CreateDate"]);
            workItem.LastUpdateDate = Convert.ToDateTime(dr["LastUpdateDate"]);
            workItem.PreviousWorkItemGuid = new Guid(Convert.ToString(dr["PreviousWorkItemGuid"]));

            SerializerData serializerData = DataProviderHelper.PopulateSerializerDataFromDataReader(dr);
            workItem.SetSerializerData(serializerData);

            return workItem;
        }

        /// <summary>
        /// 从IDataReader获取数据，生成SubTaskInfo实例，并返回该实例
        /// </summary>
        /// <param name="dr">SubTaskInfo</param>
        /// <returns>SubTaskInfo</returns>
        private SubTaskInfo PopulateSubTaskInfoFromIDataReader(IDataReader dr)
        {
            SubTaskInfo subTask = new SubTaskInfo();
            subTask.SubTaskGuid = new Guid(Convert.ToString(dr["SubTaskGuid"]));
            subTask.ParentSubTaskGuid = new Guid(Convert.ToString(dr["ParentSubTaskGuid"]));
            subTask.ActivityGuid = new Guid(Convert.ToString(dr["ActivityGuid"]));
            subTask.SubFlowGuid = new Guid(Convert.ToString(dr["SubFlowGuid"]));
            subTask.TaskGuid = new Guid(Convert.ToString(dr["TaskGuid"]));
            subTask.OrganizationID = new Guid(Convert.ToString(dr["OrganizationID"]));
            subTask.OrganizationName = Convert.ToString(dr["OrganizationName"]);
            subTask.SponsorUserName = Convert.ToString(dr["SponsorUserName"]);
            subTask.SponsorDisplayName = Convert.ToString(dr["SponsorDisplayName"]);
            subTask.SponsorFullPath = Convert.ToString(dr["SponsorFullPath"]);
            subTask.SubTaskName = Convert.ToString(dr["SubTaskName"]);
            subTask.Status = (SubTaskStatus)Convert.ToInt32(dr["Status"]);
            subTask.CreateDate = Convert.ToDateTime(dr["CreateDate"]);
            subTask.AcceptDate = Convert.ToDateTime(dr["AcceptDate"]);

            SerializerData serializerData = DataProviderHelper.PopulateSerializerDataFromDataReader(dr);
            subTask.SetSerializerData(serializerData);

            return subTask;
        }


        #endregion

    }
}
